# GF_PY3_CLASS_Finance_Data_Conv_Daily_to_Weekly_s_SQLITE3.py
# Create By GF 2024-01-22 18:16

import sqlite3  # Import Validation: SQLite3 2.6.0 from Python 3.12.0 / Requires SQLite 3.7.15 or Newer
# ..................................................
import GF_PY3_CLASS_Finance_Data_Conv_Daily_to_Weekly

# ##################################################

class Finance_Data_Conv_Daily_to_Weekly_s_SQLITE3(GF_PY3_CLASS_Finance_Data_Conv_Daily_to_Weekly.Finance_Data_Conv_Daily_to_Weekly):

    def __init__(self, SQLITE3_CONNECT, DB_TABLE:str):

        # 注意:
        # 需要在函数外创建 SQLite 3 数据库连接 (以便于复用及提高代码效率)
        # 示例:
        # >>> SQLITE3_CONNECT = sqlite3.connect("./EXAMPLE.db")  # 连接到 SQLite 3 数据库 (如果文件不存在, 会自动创建)
        # >>> SQLITE3_CONNECT.commit()                           # SQLIT3 提交事务 (查询无需 Commit, 删改需要 Commit 使事务生效)
        # >>> SQLITE3_CONNECT.close()                            # SQLIT3 关闭 Connection

        self.SQLITE3_CONNECT = SQLITE3_CONNECT
        self.DB_TABLE:str = DB_TABLE
        # ..........................................
        self.JSON_Queue_of_DY_Time:list = []   # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_DY_Open:list = []   # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_DY_High:list = []   # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_DY_Low:list = []    # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_DY_Close:list = []  # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_DY_Change:list = [] # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_DY_Volume:list = [] # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。

    def UPDATE_OR_INSERT_TEXT_Field_Value_by_ID(self, ID:int, Field:str, TEXT_Value:str) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()  # 创建一个 Cursor 对象并调用其 execute() 方法执行 SQL 命令
        # ..........................................
        This_TEXT_Value:str = TEXT_Value
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        This_TEXT_Value = "NULL" if (This_TEXT_Value == None) else This_TEXT_Value
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()  # 获取查询到的第 1 个结果
        ID_COUNT = Queried[0]        # 获取查询结果第 1 个值
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, {Field}) VALUES ({ID}, '{This_TEXT_Value}');")
            Affected_Numbers = Cursor.rowcount  # 获取受影响的行数
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET {Field} = '{This_TEXT_Value}' WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount  # 获取受影响的行数
        # ..........................................
        Cursor.close()  # SQLIT3 关闭 Cursor
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": This_TEXT_Value}

    def UPDATE_OR_INSERT_REAL_Field_Value_by_ID(self, ID:int, Field:str, REAL_Value:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        This_REAL_Value:str = REAL_Value
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        This_REAL_Value = "NULL" if (This_REAL_Value == None) else This_REAL_Value
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone() 
        ID_COUNT = Queried[0]      
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, {Field}) VALUES ({ID}, {This_REAL_Value});")
            Affected_Numbers = Cursor.rowcount  
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET {Field} = {This_REAL_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount 
        # ..........................................
        Cursor.close()  # SQLIT3 关闭 Cursor
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": This_REAL_Value}

    def UPDATE_OR_INSERT_Week_Number(self, ID:int, Time:str) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        WeeK_Number_Value:int = self.Week_Number(Time = Time)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        WeeK_Number_Value = "NULL" if (WeeK_Number_Value == None) else WeeK_Number_Value
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, week_num) VALUES ({ID}, {WeeK_Number_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET week_num = {WeeK_Number_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": WeeK_Number_Value}

    def UPDATE_OR_INSERT_DY_to_WK_Time(self, ID:int, ROW_NUM:int, DY_Time:str) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        WK_Time_Value:str = self.WK_Time(ROW_NUM = ROW_NUM, DY_Time = DY_Time)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        WK_Time_Value = "NULL" if (WK_Time_Value == None) else WK_Time_Value
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, time) VALUES ({ID}, '{WK_Time_Value}');")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET time = '{WK_Time_Value}' WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": WK_Time_Value}

    def UPDATE_OR_INSERT_DY_to_WK_Open(self, ID:int, ROW_NUM:int, DY_Open:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        WK_Open_Value = self.WK_Open(ROW_NUM = ROW_NUM, DY_Open = DY_Open)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        WK_Open_Value = "NULL" if (WK_Open_Value == None) else round(WK_Open_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, open) VALUES ({ID}, {WK_Open_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET open = {WK_Open_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": WK_Open_Value}

    def UPDATE_OR_INSERT_DY_to_WK_High(self, ID:int, ROW_NUM:int, DY_High:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        WK_High_Value = self.WK_High(ROW_NUM = ROW_NUM, DY_High = DY_High)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        WK_High_Value = "NULL" if (WK_High_Value == None) else round(WK_High_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, high) VALUES ({ID}, {WK_High_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET high = {WK_High_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": WK_High_Value}

    def UPDATE_OR_INSERT_DY_to_WK_Low(self, ID:int, ROW_NUM:int, DY_Low:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        WK_low_Value = self.WK_Low(ROW_NUM = ROW_NUM, DY_Low = DY_Low)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        WK_low_Value = "NULL" if (WK_low_Value == None) else round(WK_low_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, low) VALUES ({ID}, {WK_low_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET low = {WK_low_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": WK_low_Value}

    def UPDATE_OR_INSERT_DY_to_WK_Close(self, ID:int, ROW_NUM:int, DY_Close:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        WK_Close_Value = self.WK_Close(ROW_NUM = ROW_NUM, DY_Close = DY_Close)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        WK_Close_Value = "NULL" if (WK_Close_Value == None) else round(WK_Close_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, close) VALUES ({ID}, {WK_Close_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET close = {WK_Close_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": WK_Close_Value}

    def UPDATE_OR_INSERT_DY_to_WK_Change(self, ID:int, ROW_NUM:int, DY_Change:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        WK_Change_Value = self.WK_Change(ROW_NUM = ROW_NUM, DY_Change = DY_Change)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        WK_Change_Value = "NULL" if (WK_Change_Value == None) else round(WK_Change_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, change) VALUES ({ID}, {WK_Change_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET change = {WK_Change_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": WK_Change_Value}

    def UPDATE_OR_INSERT_DY_to_WK_Volume(self, ID:int, ROW_NUM:int, DY_Volume:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        WK_Volume_Value = self.WK_Volume(ROW_NUM = ROW_NUM, DY_Volume = DY_Volume)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        WK_Volume_Value = "NULL" if (WK_Volume_Value == None) else round(WK_Volume_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, volume) VALUES ({ID}, {WK_Volume_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET volume = {WK_Volume_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": WK_Volume_Value}

# EOF Signed by GF.
